import random,time,requests,json

# from db import db
from model.models import db
from flask import Blueprint, request, jsonify, send_file, redirect
from flask_restful import Api, Resource,reqparse
from captcha.image import ImageCaptcha
from utils.myredis import r
from utils.sms import send_message
from utils.myjwt import myjwt
from model.models import *

course_dp = Blueprint('course_dp',__name__)
api = Api(course_dp)

#类别
@course_dp.route("/ctype")
def ctype():
    ctypelist = []
    # 获取缓存中数据
    r.del_key("ctype")
    ctype = r.get_str("ctype")
    # 判断是否存在
    if ctype:
        ctypelist = json.loads(ctype)
    else:
        sql = Ctype.query.all()
        for i in sql:
            d = {"name":i.name}
            ctypelist.append(d)
        # 存入缓存
        r.set_str("ctype", json.dumps(ctypelist))
    return ctypelist


# 标签
def getTaglist():
    taglist = []
    r.del_key("tage")
    tag = r.get_str("tage")
    if tag:
        taglist = json.loads(tag)
    else:
        sql = Tags.query.all()
        for i in sql:
            d = {"name":i.name}
            taglist.append(d)
        r.set_str("tage", json.dumps(taglist))
    return taglist

#分页
@course_dp.route("/getHomeGourse")
def getHomeGourse1():
    # 获取类型
    ctypelist = ctype()
    # 获取标签
    taglist = getTaglist()

    # 获取当前页面
    page = request.args.get("page")
    # 获取查询条件
    typeid = request.args.get("type")
    tagid = request.args.get("tagid")
    online = request.args.get("online")
    # 排序条件
    orderbyid = request.args.get("orderbyid")
    print(tagid)

    where = ""
    if int(typeid) > 0:
        where = where + " and c.ctype=" + typeid

    if int(online) > 0:
        where = where + " and c.views=" + online

    # res = db.session.execute("select * from user where id=1")
    # print(res.fetchall())
    # print(res.fetchone())

    if int(tagid) > 0:
        tsql = "select count(c.id) as tcount from course as c inner join course_tag as cl on cl.coursesid=c.id  where cl.tagid=" + tagid
        sql = "select * from course as c inner join course_tag as cl on cl.coursesid=c.id  where cl.tagid=" + tagid
    else:
        sql = "select * from course as c where 1=1"
        tsql = "select count(c.id) as tcount from course as c  where 1=1"

    if where:
        sql = sql + " " + where
        tsql = tsql + " " + where

    if int(orderbyid) > 0:
        if int(orderbyid) == 1:
            sql = sql + " order by c.id desc"
        else:
            sql = sql + " order by c.is_publish desc"

    # 查询总共有多少条
    tsql = db.session.execute(tsql)
    print(tsql)
    total = 0
    for i in tsql:
        total = i[0]

    page_size = 2
    start = (int(page) - 1) * page_size
    clist = sql + " limit %d,%d" % (start, page_size)
    clist = db.session.execute(clist)
    # courselist.fetchall()
    courselist = []
    for i in clist:
        d = {
            "id":i[0],
            "create_time":i[1],
            "update_time":i[2],
            "name":i[3],
            "url":i[4],
            "descrip":i[5],
            "tcount":i[6],
            "views":i[7],
            "is_publish":i[8],
            "ctype":i[9]
        }
        courselist.append(d)

    return jsonify({"code": 200, "tlist": ctypelist, 'taglist': taglist, 'clist': courselist, "total": total,
                    "page_size": page_size})